<?php
/* -------------------------------------------------------------------------------------
* 	ID:						$Id: idealo.php 11 2013-09-21 11:43:11Z phone.mueller@googlemail.com $
* 	Letzter Stand:			$Revision: 11 $
* 	zuletzt geaendert von:	$Author: siekiera $
* 	Datum:					$Date: 2013-09-21 11:43:11 +0000 (Sat, 21 Sep 2013) $
*
* 	SEO:mercari by Siekiera Media
* 	http://www.seo-mercari.de
*
* 	Copyright (c) since 2011 SEO:mercari
* --------------------------------------------------------------------------------------
* 	based on:
* 	(c) 2000-2001 The Exchange Project  (earlier name of osCommerce)
* 	(c) 2002-2003 osCommerce - www.oscommerce.com
* 	(c) 2003     nextcommerce - www.nextcommerce.org
* 	(c) 2005     xt:Commerce - www.xt-commerce.com
*
* 	Released under the GNU General Public License
* ----------------------------------------------------------------------------------- */

defined( '_VALID_XTC' ) or die( 'Direct Access to this location is not allowed.' );

define('MODULE_IDEALO_TEXT_DESCRIPTION', 'Export - Idealo (Semikolon getrennt)');
define('MODULE_IDEALO_TEXT_TITLE', 'Idealo - CSV');
define('MODULE_IDEALO_FILE_TITLE' , '<hr noshade>Dateiname');
define('MODULE_IDEALO_FILE_DESC' , 'Geben Sie einen Dateinamen ein, falls die Exportadatei am Server gespeichert werden soll.<br>(Verzeichnis export/)');
define('FIELDSEPARATOR', '<b>Spaltentrenner</b>');
define('FIELDSEPARATOR_HINT', 'Beispiel:<br>;&nbsp;&nbsp;&nbsp;(Semikolon)<br>,&nbsp;&nbsp;&nbsp;(Komma)<br>\t&nbsp;&nbsp;(Tab)<br>...<br>Wird das Feld leer gelassen, wird Tab als Trenner genutzt.');
define('QUOTING','<b>Quoting</b>');
define('QUOTING_HINT','Beispiel:<br>"&nbsp;&nbsp;&nbsp;(Anf&uuml;hrungszeichen)<br>\'&nbsp;&nbsp;&nbsp;(Hochkomma)<br>#&nbsp;&nbsp;(Raute)<br>... <br>Wird das Feld leer gelassen, wird nicht gequotet.');
define('SHIPPINGCOMMENT', '<b>Versandkommentar</b>');
define('SHIPPINGCOMMENT_HINT', 'Max. 100 Zeichen');
define('FREESHIPPINGCOMMENT', '<b>Kommentar zur Versankosten-Grenze</b>');
define('FREESHIPPINGCOMMENT_HINT', 'Wird bei allen Angeboten angezeigt, die unter der Versandkostenfreiheits-Grenze liegen.<br>Max. 100 Zeichen');
define('LANGUAGE', '<b>Export f&uuml;r</b>');
define('LANGUAGE_HINT', 'Beispiel:<br>DE (Deutschland)<br>AT (&Ouml;sterreich)<br>...<br>Es sollten(!) die Sprachen genutzt werden, die auch bei den Versandkosten etc. korrekt hinterlegt sind.<br>Wird das Feld leer gelassen, wird \'DE\' benutzt.');
define('MODULE_IDEALO_STATUS_DESC','Modulstatus');
define('MODULE_IDEALO_STATUS_TITLE','Status');
define('MODULE_IDEALO_CURRENCY_TITLE','W&auml;hrung');
define('MODULE_IDEALO_CURRENCY_DESC','Welche W&auml;hrung soll exportiert werden?');
define('EXPORT_YES','Nur Herunterladen');
define('EXPORT_NO','Am Server Speichern');
define('CURRENCY','<hr noshade><b>W&auml;hrung:</b>');
define('CURRENCY_DESC','W&auml;hrung in der Exportdatei');
define('EXPORT','Bitte den Sicherungsprozess AUF KEINEN FALL unterbrechen. Dieser kann einige Minuten in Anspruch nehmen.');
define('EXPORT_TYPE','<hr noshade><b>Speicherart:</b>');
define('EXPORT_STATUS_TYPE','<hr noshade><b>Kundengruppe:</b>');
define('EXPORT_STATUS','Bitte w&auml;hlen Sie die Kundengruppe, die Basis f&uuml;r den Exportierten Preis bildet. (Falls Sie keine Kundengruppenpreise haben, w&auml;hlen Sie <i>Gast</i>):</b>');
define('CAMPAIGNS','<hr noshade><b>Kampagnen:</b>');
define('CAMPAIGNS_DESC','Mit Kampagne zur Nachverfolgung verbinden.');
define('DATE_FORMAT_EXPORT', '%d.%m.%Y');  // this is used for strftime()
define('DISPLAY_PRICE_WITH_TAX','true');
define('COMMENTLENGTH', 100);


if( isset($_POST['separator_input']) && $_POST['separator_input'] != '' ) {
	$separator = $_POST['separator_input'];
} else {
	// if nothing is entered by the admin: $separator gets \t as default
	$separator = "\t";
}

// is a specific quoting character set?
if( isset($_POST['quoting_input']) && $_POST['quoting_input'] != '' ) {
	$quoting = stripcslashes($_POST['quoting_input']);
} else {
	// if nothing is entered by the admin: $quoting is disabled
	$quoting = "";
}

// is a specific language set?
if( isset($_POST['language_input']) && $_POST['language_input'] != '' ) {
	$country_sc = stripslashes($_POST['language_input']);
} else {
	// if nothing is entered by the admin: $quoting is disabled
	$country_sc = "DE";
}


// check if freeshippinglimit_input is already in db
$shipping_comment_db = $db->db_query("SELECT configuration_value FROM  ".TABLE_CONFIGURATION." WHERE configuration_key = 'MODULE_IDEALO_SHIPPINGCOMMENT' LIMIT 1");

// is shipping comment set?
// do not exceed COMMENTLENGTH
if( isset( $_POST['shippingcomment_input']) && ( strlen($_POST['shippingcomment_input']) <= COMMENTLENGTH ) ) {
	// does a dataset exist?
	if($shipping_comment_db !== false ) {

		// update value if $_POST['freeshippinglimit_input'] != $freeshipping_comment_db
		if( $_POST['shippingcomment_input'] != $shipping_comment_db->fields['configuration_value'] ) {
			$db->db_query("UPDATE ".TABLE_CONFIGURATION."
					      set configuration_value = '".$_POST['shippingcomment_input']."'
					      WHERE configuration_key = 'MODULE_IDEALO_SHIPPINGCOMMENT'");
		}
	} else {
		// insert data
		$db->db_query("INSERT INTO ".TABLE_CONFIGURATION."
					  (configuration_key, configuration_value, configuration_group_id, sort_order, set_function, date_added)
					  VALUES ('MODULE_IDEALO_SHIPPINGCOMMENT', '".$_POST['shippingcomment_input']."', 6, 1, '', now()) ");
	}

	$shipping_comment_input = stripslashes($_POST['shippingcomment_input']);

} else
	$shipping_comment_input = "";

// check if freeshippinglimit_input is already in db
$freeshipping_comment_db = $db->db_query("SELECT configuration_value from  ".TABLE_CONFIGURATION." WHERE configuration_key = 'MODULE_IDEALO_FREESHIPPINGCOMMENT' LIMIT 1");

// is free shipping comment set?
// do not exceed COMMENTLENGTH
if( isset( $_POST['freeshippingcomment_input']) && ( strlen($_POST['freeshippingcomment_input']) <= COMMENTLENGTH ) ) {

	// does a dataset exist?
	if($freeshipping_comment_db !== false) {

		// update value if $_POST['freeshippingcomment_input'] != $freeshipping_comment_db
		if( $_POST['freeshippingcomment_input'] != $freeshipping_comment_db->fields['configuration_value'] ) {
			$db->db_query("UPDATE ".TABLE_CONFIGURATION."
					      set configuration_value = '".$_POST['freeshippingcomment_input']."'
					      WHERE configuration_key = 'MODULE_IDEALO_FREESHIPPINGCOMMENT'");
		}
	} else {
		// insert data
		$db->db_query("INSERT INTO ".TABLE_CONFIGURATION."
					  (configuration_key, configuration_value, configuration_group_id, sort_order, set_function, date_added)
					  VALUES ('MODULE_IDEALO_FREESHIPPINGCOMMENT', '".$_POST['freeshippingcomment_input']."', 6, 1, '', now()) ");
	}

	$freeshipping_comment_input = stripslashes($_POST['freeshippingcomment_input']);
} else
	$freeshipping_comment_input = "";

// file config
define('SEPARATOR',  $separator);  		// character that separates the data
define('QUOTECHAR',  $quoting);    		// character to quote the data
define('COUNTRY_SC', $country_sc);   		// country the shipping costs are for
define('DISPLAYINACTIVEMODULES', true); // display modules that are not active but in the payment array
										// advantage: structure of the file hardly changes

define('SHIPPINGCOMMENT_INPUT', $shipping_comment_input);
define('FREESHIPPINGCOMMENT_INPUT', $freeshipping_comment_input);
define('SHOWFREESHIPPINGLIMITCOMMENT', true); // set 'true' to show comment for free shipping limit

require_once(DIR_FS_CATALOG.DIR_WS_CLASSES.'class.price.php');

class idealo {

    // these attributes have to be public, as module_export.php uses them directly ...
    public $code;
    public $title;
    public $description;
    public $enabled;
	var $db;

	// all payment (and its status) that should be displayed in the csv
	// if a payment is 'false', the column in the csv stays empty
	// the key needs to be the same as it is used in the db for the entry in `configuration_key` in the table ".TABLE_CONFIGURATION."
	private $payment = array('MONEYORDER'   => array('active' => false,
													 'title' => 'Vorkasse'),
							 'COD' 			=> array('active' => false,
													 'title' => 'Nachnahme'),
							 'INVOICE' 		=> array('active' => false,
													 'title' => 'Rechnung'),
							 'CC' 			=> array('active' => false,
													 'title' => 'Kreditkarte'),
							 'BANKTRANSFER' => array('active' => false,
													 'title' => 'Lastschrift'),
							 'PAYPAL' 		=> array('active' => false,
													 'title' => 'PayPal'),
							 'MONEYBOOKERS' => array('active' => false,
													 'title' => 'Moneybookers'),
							 'UOS_GIROPAY'  => array('active' => false,
											 		 'title' => 'Giropay')
							);

	// types of shipping cost and 2-3 properties
	// this is neccessary to get the correct VALUES for "cash on delivery"
	private $paymentTable = false;        // table sc
	private $paymentTableMode = 'weight'; // default mode for table sc

	private $paymentItem  = false;        // sc per item
	private $paymentFlat  = false;        // flat rate sc

	private $freeShipping = false;        // no sc
	private $freeShippingValue;           // calculates when shipping is free

	// table shipping
	private $paymentTableValues = array();

	// default shipping cost (does NOT count when modul "table shipping cost" is active)
	private $standardShippingCost = 0.00;

    public function __construct() {
    	global $db;

		$this->code = 'idealo';
		$this->img = 'images/export/idealo.gif';
		$this->title = MODULE_IDEALO_TEXT_TITLE;
		$this->description = MODULE_IDEALO_TEXT_DESCRIPTION;
		$this->sort_order = MODULE_IDEALO_SORT_ORDER;
		$this->enabled = ((MODULE_IDEALO_STATUS == 'True') ? true : false);
		$this->CAT = array();
		$this->PARENT = array();
		$this->productsPrice = 0;
		$this->db = $db;

		$this->checkActivePayment();

		$this->checkStandardShippingCostsOption();
    }

	/**
	 * Checks which payment method (pm) is active
	 * If a pm is not active, it wont appear in the csv
	 *
	 * A pm is only active when the entry 'MODULE_PAYMENT_{paymentmethod}_STATUS' in the table ".TABLE_CONFIGURATION." exists
	 * and the `configuration_value` is 'true'
	 */
	private function checkActivePayment() {
		// run through every payment method
		foreach($this->payment as $singlePayment => $status) {
			// is the pm active?
			$checkPayment = $this->db->db_query("	SELECT 
														COUNT(*) AS found
													FROM 
														".TABLE_CONFIGURATION."
													WHERE 
														configuration_key LIKE 'MODULE_PAYMENT_{$singlePayment}_STATUS'
													AND 
														configuration_value LIKE 'True';");

			// if the result is > 0, the pm is active
			if($checkPayment->_numOfRows > 0)
				$this->payment[$singlePayment]['active'] = true;
		}
	}

	/**
	 * Method returns the shipping cost for a specific payment method
	 *
	 * @param string $payment
	 * @param double|null $price
	 * @param double|null $offerWeight
	 *
	 * @return double|string shipping costs else an empty string
	 */
	private function getShippingCosts($payment, $price = null, $offerWeight = null) {
		$shippingCost = '';

		// is the is payment active?
		if( $this->payment[$payment]['active'] === true ) {

			// is free delivery active and price equal or higher than the limit?
			if(($this->freeShipping) === true && ($price >= $this->freeShippingValue)) {
				$shippingCost = 0.00;
			}
			// is at least one shipping option active?
			elseif(($this->paymentTable === true) || ($this->paymentItem === true) || ($this->paymentFlat === true) ) {

				// first of all we get the standard shipping costs (default sc, per item or table)

				// are the table shipping costs active? Check which table payment option is active
				if($this->paymentTable === true) {

					// run through the table VALUES and check which weight / price matches the offer
					switch($this->paymentTableMode) {
						case 'weight':
							$offerCompareValue = $offerWeight;
						break;
						case 'price':
							$offerCompareValue = $price;
						break;
					}

					if(is_array($this->paymentTableValues) && $offerCompareValue != null) {

						foreach($this->paymentTableValues as $tableModeValue => $tablePrice) {
							// stop the loop if sth. matched
							if($offerCompareValue <= $tableModeValue) {
								$shippingCost = $tablePrice;
								break;
							}
						}

						// If no weight / price was matched accordingly, the last entry in the array is taken
						if($shippingCost == '') {
							end($this->paymentTableValues); // Zeiger an letzte Stelle bewegen
							$shippingCost = current($this->paymentTableValues); // Wert ausgeben auf den der Zeiger aktuell zeigt
							reset($this->paymentTableValues); // Setze Zeiger wieder in Ausgangsposition
						}

					} else {
						// if the table sc VALUES are not correct or the weight / price is null => nothing shall appear in the csv
						$shippingCost = '';
					}
				} else {
					$shippingCost = $this->standardShippingCost;
				}
			}

			// cod needs additional calculation
			// the additional cod_fee (if active) depends on the shipping option that is active as the fee can differ
			if($payment == 'COD') {
				 // check if extra fee for "Cash on Delivery" is active

				 // 1. get the db data
				$result = $this->db->db_query("	SELECT 
													`configuration_value` AS `cod_fee_status`
												FROM 
													".TABLE_CONFIGURATION."
													WHERE 
												`configuration_key` LIKE 'MODULE_ORDER_TOTAL_COD_FEE_STATUS';");

				// 2. is the fee status active?
				if(isset($result->fields['cod_fee_status']) && $result->fields['cod_fee_status'] == 'true') {
					$modul = '';
					// which shipping option is active?
					if(($this->freeShipping) === true && ($price >= $this->freeShippingValue)) {
						$modul = 'MODULE_ORDER_TOTAL_FREEAMOUNT_FREE';
					} elseif($this->paymentTable === true) {
						$modul = 'MODULE_ORDER_TOTAL_COD_FEE_TABLE';
					} elseif($this->paymentItem === true) {
						$modul = 'MODULE_ORDER_TOTAL_COD_FEE_ITEM';
					} elseif($this->paymentFlat === true) {
						$modul = 'MODULE_ORDER_TOTAL_COD_FEE_FLAT';
					}
					
					unset($result);
					$result = $this->db->db_query("	SELECT 
															`configuration_value` AS `cod_cost`
														FROM 
															".TABLE_CONFIGURATION."
														WHERE 
															`configuration_key` LIKE '{$modul}';");
					
					$result = array();

					// Are there any costs?
					if(isset($result['cod_cost']) && $result->fields['cod_cost'] != '') {
						// get the value for the country
						preg_match_all('/'.COUNTRY_SC.':([^,]+)?/', $result->fields['cod_cost'], $match);

						// $match[1][0] contains the result in the form of (e.g.) 7.00 or 7
						// to make sure that mistakes like 7.00:9.99 (correct would be 7,00:9.99) are also handled, we check for the colon
						if(preg_match('/:/', $match[1][0])) {
							$tmpArr = explode(':', $match[1][0]);
							$codCost = $tmpArr[0];
						} else
							$codCost = $match[1][0];

						// de we ge a useful value?
						if(isset($codCost) && $codCost != NULL && is_numeric($codCost)) {
							$shippingCost += $codCost;
						}
					}
				}

			}

			// calculate taxes
	        if (DISPLAY_PRICE_WITH_TAX == 'true') {
	            $tax = get_tax_rate_export(MODULE_SHIPPING_FLAT_TAX_CLASS, STORE_COUNTRY, MODULE_SHIPPING_FLAT_ZONE);
	            $shippingCost = add_tax($shippingCost, $tax);
	        }

			// format and round numbers
			$shippingCost = number_format($shippingCost, 2, '.', '');
		}
		return $shippingCost;
	}

	/**
	 * Method checks which standard shipping option is active.
	 *  - is the freeShipping active, $this->freeShipping = true
	 *  - is table sc option active, $this->paymentTable = true
	 *  - is table sc option NOT active, but sc per item, $this->paymentItem = true
	 *  - are neither table sc NOR sc per item, BUT the default sc active, $this->paymentFlat = true
	 *
	 * This is important for cash on delivery as there are different fee options possible.
	 */

	private function checkStandardShippingCostsOption() {
		// free shipping?
		if($this->checkShippingCostOption('FREEAMOUNT') > 0  ) {
			$this->freeShipping = true;

			// catch the limit for free shipping
			$result = $this->db->db_query("	SELECT 
												`configuration_value` AS `freeShippingValue`
											FROM 
												".TABLE_CONFIGURATION."
											WHERE 
												`configuration_key` LIKE 'MODULE_SHIPPING_FREEAMOUNT_AMOUNT';");

			// if the value of the free shipping value is not set, its 0.00 ( = always free)
			if(isset($result->fields['freeShippingValue']) && is_numeric($result->fields['freeShippingValue']))
				$this->freeShippingValue = $result->fields['freeShippingValue'];
			else
				$this->freeShippingValue = 0.00;
		}

		if($this->checkShippingCostOption('TABLE') > 0) {
			// table shipping cost
			$this->paymentTable = true;

			// set the VALUES for table sc to get the correct sc for every offer
			$this->setPaymentTableValues();

		} elseif($this->checkShippingCostOption('ITEM') > 0) {
			// sc per item
			$this->paymentItem = true;

			// set the standard shipping costs
			$this->setStandardShippingCosts();
		} elseif($this->checkShippingCostOption('FLAT') > 0) {
			// flat sc
			$this->paymentFlat = true;

			// set the standard shipping costs
			$this->setStandardShippingCosts();
		}
	}

	/**
	 * Method sets the standard shipping costs (NOT the one for "table sc")
	 * The standard sc can consist of the "flat sc" OR the "sc per item"
	 * as the offer listing in the csv refers to ONE offer
	 */

	private function setStandardShippingCosts() {
		$shippingModul = '';

		if($this->paymentItem === true)
			$shippingModul = 'MODULE_SHIPPING_ITEM_COST';
		else
			$shippingModul = 'MODULE_SHIPPING_FLAT_COST';

		$result = $this->db->db_query("SELECT `configuration_value` AS `standard_sc`
										FROM ".TABLE_CONFIGURATION."
										WHERE `configuration_key` LIKE '{$shippingModul}';");

		// if $result['standard_sc'] is not set, $this->standardShippingCost stays empty (to be on the safe side)
		if(isset($result->fields['standard_sc']))
			$this->standardShippingCost = $result->fields['standard_sc'];
		else
			$this->standardShippingCost = '';
	}

	/**
	 * Method checks if a specific shipping costs option is activated
	 *
	 * @param string $option
	 *
	 * @return integer 0 when nothing is found, otherwise a number bigger than 0
	 */

	 private function checkShippingCostOption($option) {
	   // transform to uppercase
	   $option = strtoupper($option);
	   $result = $this->db->db_query("SELECT COUNT(*) AS found
									    FROM ".TABLE_CONFIGURATION."
									    WHERE configuration_key LIKE 'MODULE_SHIPPING_{$option}_STATUS'
									    AND configuration_value LIKE 'True';");

	   if (isset($result->fields['found']) && $result->fields['found'] > 0) { // module is active,  check allowed countries
	     $countryOk = $this->db->db_query("SELECT COUNT(*) AS found
										FROM ".TABLE_CONFIGURATION."
										WHERE configuration_key LIKE 'MODULE_SHIPPING_{$option}_ALLOWED' AND
										(configuration_value LIKE '%".COUNTRY_SC."%' OR configuration_value=''); ");

	    // if $countryOk['found'] is not set, 0 (country is not activated) will be returned
	    return (isset($countryOk->fields['found'])) ? $countryOk->fields['found'] : 0;
	   } else
	    return 0;
	 }


	/**
	 * Method sets the "table shipping costs" values
	 */
	private function setPaymentTableValues() {
		$explodedValues = array();

		// take the data from the db
		$result = $this->db->db_query("SELECT `configuration_value` AS `table_values`
								   		FROM ".TABLE_CONFIGURATION."
								   		WHERE `configuration_key` LIKE 'MODULE_SHIPPING_TABLE_COST';");

		// the result shouldnt be empty
		// otherwise $this->paymentTableValues stays empty
		// example string: 25:8.50,50:5.50,10000:0.00

		if( isset($result->fields['table_values']) && $result->fields['table_values'] != '') {
			// split die Value at the comma
			$explodedValues = explode(',', $result->fields['table_values']);

			// run through the VALUES and split again at the colon
			// the key is the weight / price and the value is the sc
			foreach($explodedValues as $values) {
				$tmpAr = array();
				$tmpAr = explode(":", $values);

				// are there only numbers?
				if( is_numeric($tmpAr[0]) && is_numeric($tmpAr[1]) ) {
					$this->paymentTableValues[$tmpAr[0]] = $tmpAr[1];
				}
				unset($tmpAr);
			}
		}

		// check what param is used for "table sc": weight or price
		$result = $this->db->db_query("SELECT `configuration_value` AS `table_mode`
										FROM ".TABLE_CONFIGURATION."
										WHERE `configuration_key` LIKE 'MODULE_SHIPPING_TABLE_MODE';");

		if(isset($result->fields['table_mode']) && $result->fields['table_mode'] != '') {
			$this->paymentTableMode = $result->fields['table_mode'];
		}
	}

	/**
	 * Methode creates the content of the csv
	 *
	 * @param string $file
	 */
    public function process($file) {
		$schema = '';
        @set_time_limit(0);
        $price = new price($_POST['currencies'],$_POST['status'], $this->db);

        $schema .= QUOTECHAR.'artikelId'.QUOTECHAR.SEPARATOR .
				   QUOTECHAR.'hersteller'.QUOTECHAR.SEPARATOR .
        		   QUOTECHAR.'bezeichnung'.QUOTECHAR.SEPARATOR .
        		   QUOTECHAR.'kategorie'.QUOTECHAR.SEPARATOR .
        		   QUOTECHAR.'beschreibung_kurz'.QUOTECHAR.SEPARATOR .
        		   QUOTECHAR.'beschreibung_lang'.QUOTECHAR.SEPARATOR .
        		   QUOTECHAR.'bild'.QUOTECHAR.SEPARATOR .
        		   QUOTECHAR.'deeplink'.QUOTECHAR.SEPARATOR .
        		   QUOTECHAR.'preis'.QUOTECHAR.SEPARATOR .
        		   QUOTECHAR.'ean'.QUOTECHAR.SEPARATOR .
        		   QUOTECHAR.'lieferzeit'.QUOTECHAR.SEPARATOR;

		// run through the payment method titles to display them in the header
		foreach($this->payment as $payment => $options) {
			// display only the payment methods that are active (if this is desired)
			if($options['active'] === true || DISPLAYINACTIVEMODULES === true) {
				$schema .= QUOTECHAR.$options['title'].QUOTECHAR.SEPARATOR;
			}
		}

		// shipping comment
		$schema .= QUOTECHAR.'Versandkommentar'.QUOTECHAR.SEPARATOR;

		// free shipping comment (if active)
		if( ($this->freeShipping === true) && (SHOWFREESHIPPINGLIMITCOMMENT === true) ) {
			$schema .= QUOTECHAR.'Kommentar Versandkosten-Grenze'.QUOTECHAR.SEPARATOR;
		}

        $schema .= "\n";

        $products =$this->db->db_query("SELECT
				                             p.products_id,
				                             pd.products_name,
				                             pd.products_description,pd.products_short_description,
				                             p.products_model,p.products_ean,
				                             p.products_image,
				                             p.products_price,
				                             p.products_status,
				                             p.products_date_available,
				                             p.products_shippingtime,
				                             p.products_discount_allowed,
				                             pd.products_meta_keywords,
				                             p.products_tax_class_id,
				                             p.products_date_added,
				                             p.products_weight,
				                             m.brand_name
				                         FROM
				                             ".TABLE_PRODUCTS." p LEFT JOIN
				                             ".TABLE_BRAND." m
				                           ON p.brand_id = m.brand_id LEFT JOIN
				                             ".TABLE_PRODUCTS_DESCRIPTION." pd
				                           ON p.products_id = pd.products_id AND
				                            pd.language_id = '".$_SESSION['languages_id']."' LEFT JOIN
				                             ".TABLE_SPECIALS." s
				                           ON p.products_id = s.products_id
				                         WHERE
				                           p.products_status = 1
				                         ORDER BY
				                            p.products_date_added DESC,
				                            pd.products_name");


        while(!$products->EOF) {

            $products_price = $price->GetPrice($products->fields['products_id'], false, 1, $products->fields['products_tax_class_id']);
            $this->productsPrice = $products_price;

            $categorie = $this->db->db_query("SELECT
													categories_id
												FROM
													".TABLE_PRODUCTS_TO_CATEGORIES."
												WHERE
													products_id='".$products->fields['products_id']."'");

            while(!$categorie->EOF) {
				$categories = $categorie->fields['categories_id'];
				$categorie->MoveNext();
			}


            // remove trash

            // characters that should be replaced
			$spaceToReplace = array("<br>", "<br />", "\n", "\r", "\t", "\v", chr(13)); // replace by space
			$commaToReplace = array("'");  												// replace by comma
			$quoteToReplace = array("&quot,", "&qout,");								// replace by quote ( " )

			// replace characters and cut to the appropriate length
            $products_description = strip_tags($products->fields['products_description']);
			$products_description = str_replace($spaceToReplace," ",$products_description);
			$products_description = str_replace($commaToReplace,", ",$products_description);
			$products_description = str_replace($quoteToReplace," \"",$products_description);

			$products_description = substr($products_description, 0, 65536);

            $products_short_description = strip_tags($products->fields['products_short_description']);
			$products_short_description = str_replace($spaceToReplace," ",$products_short_description);
			$products_short_description = str_replace($commaToReplace,", ",$products_short_description);
			$products_short_description = str_replace($quoteToReplace," \"",$products_short_description);

            $products_short_description = substr($products_short_description, 0, 255);

			$cat = $this->buildCAT($categories);


			if ($products->fields['products_image'] != '')
			    $image = HTTP_CATALOG_SERVER.DIR_WS_CATALOG_ORIGINAL_IMAGES .$products->fields['products_image'];
			else
			    $image = '';

            //create content
            $schema .= QUOTECHAR.$products->fields['products_id'].QUOTECHAR.SEPARATOR .
                       QUOTECHAR.$products->fields['brand_name']. QUOTECHAR.SEPARATOR .
                       QUOTECHAR.$products->fields['products_name'].QUOTECHAR.SEPARATOR .
                       QUOTECHAR.substr($cat,0,strlen($cat)-2).QUOTECHAR. SEPARATOR .
                       QUOTECHAR.$products_short_description.QUOTECHAR.SEPARATOR .
                       QUOTECHAR.$products_description.QUOTECHAR.SEPARATOR .
                       QUOTECHAR.$image.QUOTECHAR.SEPARATOR .
                       QUOTECHAR.HTTP_CATALOG_SERVER.DIR_WS_CATALOG.'product_info.php?'.$_POST['campaign'].product_link($products->fields['products_id'], $products->fields['products_name']).QUOTECHAR.SEPARATOR .
                       QUOTECHAR.number_format($products_price,2,'.','').QUOTECHAR.SEPARATOR .
                       QUOTECHAR.$products->fields['products_ean'].QUOTECHAR.SEPARATOR .
                       QUOTECHAR.get_shipping_status_name($products->fields['products_shippingtime']).QUOTECHAR.SEPARATOR;

					   // free shipping costs AND free sc comment available?
					   $showScFreeComment = false;

					   // run through the payment methods to display the fee
				       foreach($this->payment as $singlePayment => $options) {
					   		// display only the payment fee that is active (if this is desired)
				        	if($options['active'] === true || DISPLAYINACTIVEMODULES === true) {
								$sc = $this->getShippingCosts($singlePayment, $products_price, $products->fields['products_weight']);
								$schema .= QUOTECHAR.$sc.QUOTECHAR.SEPARATOR;

				        		// if there's one payment with sc > 0.00, display the sc free comment
				        		// exception: cash on delivery
				        		if( $singlePayment != 'COD' && $sc > 0.00 )
				        			$showScFreeComment = true;
				        	}
				        }

			$schema .= QUOTECHAR.SHIPPINGCOMMENT_INPUT.QUOTECHAR.SEPARATOR;

			// Only if free shipping costs are available AND SHOWFREESHIPPINGCOMMENTCOMMENT is set to true
			if( ($this->freeShipping === true) && SHOWFREESHIPPINGLIMITCOMMENT === true  ) {
				// is shipping of the offer for free?
				if($showScFreeComment === true)
					$schema .= QUOTECHAR.FREESHIPPINGCOMMENT_INPUT.QUOTECHAR.SEPARATOR;
				else
					$schema .= QUOTECHAR.''.QUOTECHAR.SEPARATOR;
			}

			$schema .= "\n";
			$products->MoveNext();
        }
        // create File
          $fp = fopen(DIR_FS_DOCUMENT_ROOT.'export/'.$file, "w+");
          fputs($fp, $schema);
          fclose($fp);

	  if( isset($_POST['export']) && $_POST['export'] == 'yes' ) {
            // send File to Browser
            $extension = substr($file, -3);
            $fp = fopen(DIR_FS_DOCUMENT_ROOT.'export/'.$file,"rb");
            $buffer = fread($fp, filesize(DIR_FS_DOCUMENT_ROOT.'export/'.$file));
            fclose($fp);
            header('Content-type: application/x-octet-stream');
            header('Content-disposition: attachment; filename='.$file);
            echo $buffer;
            exit;
	  }

    }

   /**
    * Methods creates the Categorie for a categorieId
    *
    * @param int $catID
    * @return string Category
    */
   private function buildCAT($catID) {

        if(isset($this->CAT[$catID]))
			return $this->CAT[$catID];
        else {
           $cat=array();
           $tmpID=$catID;

           while($this->getParent($catID)!=0 || $catID!=0) {
                $cat_data = $this->db->db_query("SELECT categories_name FROM ".TABLE_CATEGORIES_DESCRIPTION." WHERE categories_id='".$catID."' and language_id='".$_SESSION['languages_id']."'");

                $catID = $this->getParent($catID);
                $cat[] = $cat_data->fields['categories_name'];
           }
			$catStr='';
			for ($i=count($cat);$i>0;$i--)
			$catStr .= $cat[$i-1].' > ';

			$this->CAT[$tmpID]=$catStr;
			return $this->CAT[$tmpID];
        }
    }

	/**
	 * Method returns the parentId of a categoryId
	 *
	 * @param int $catID
	 * @return int parent id of the category
	 */
   private function getParent($catID) {
		if (isset($this->PARENT[$catID]))
			return $this->PARENT[$catID];
		else {
			$parent = $this->db->db_query("SELECT parent_id FROM ".TABLE_CATEGORIES." WHERE categories_id='".$catID."'");
			$this->PARENT[$catID] = $parent->fields['parent_id'];
			return  $parent->fields['parent_id'];
		}
	}

	/**
	 * Method prepares the text that is displayed at the detailed options on module_export.php
	 */
    public function display() {

	    $customers_statuses_array = get_customers_statuses();

	    // build Currency Select
	    $curr='';
	    $currencies = $this->db->db_query("SELECT code FROM ".TABLE_CURRENCIES);
	    while(!$currencies->EOF) {
	    	$curr.=draw_radio_field('currencies', $currencies_data->fields['code'],true).$currencies_data->fields['code'].'<br />';
	    	$currencies->MoveNext();
	    }

	    $campaign_array = array(array('id' => '', 'text' => TEXT_NONE));
		$campaign = $this->db->db_query("SELECT campaigns_name, campaigns_refID from ".TABLE_CAMPAIGNS." ORDER BY campaigns_id");
		while(!$campaign->EOF) {
			$campaign_array[] = array ('id' => 'refID='.$campaign->fields['campaigns_refID'].'&', 'text' => $campaign->fields['campaigns_name']);
			$campaign->MoveNext();
		}

		// get free shipping comment from db
		if($this->freeShipping === true && SHOWFREESHIPPINGLIMITCOMMENT === true ) {
			$freeshipping_comment_db = $this->db->db_query("SELECT configuration_value from  ".TABLE_CONFIGURATION." WHERE configuration_key = 'MODULE_IDEALO_FREESHIPPINGCOMMENT' LIMIT 1");

			$freeValue_Input_Text = ( $this->freeShippingValue != '' ) ? $freeshipping_comment_db->fields['configuration_value'] : '';
			$freeshippingHTML = FREESHIPPINGCOMMENT.'<br>'.FREESHIPPINGCOMMENT_HINT.'<br>'.draw_input_field('freeshippingcomment_input', "{$freeValue_Input_Text}").'<br><br>';
		} else
			$freeshippingHTML = '';

		// get shipping comment from db
		$shipping_comment_db = $this->db->db_query("SELECT configuration_value from  ".TABLE_CONFIGURATION." WHERE configuration_key = 'MODULE_IDEALO_SHIPPINGCOMMENT' LIMIT 1");
		$shipping_comment_text = ( $shipping_comment_db !== false ) ? $shipping_comment_db->fields['configuration_value'] : '';

	    return array('text' =>
	    						'<br>'.FIELDSEPARATOR.'<br>' .
	    						FIELDSEPARATOR_HINT.'<br>' .
	    						draw_small_input_field('separator_input', ';').'<br><br>' .
	    						QUOTING.'<br>' .
	    						QUOTING_HINT.'<br>' .
	    						draw_small_input_field('quoting_input', '"').'<br><br>' .
								SHIPPINGCOMMENT.'<br>' .
								SHIPPINGCOMMENT_HINT.'<br>' .
								draw_input_field('shippingcomment_input', $shipping_comment_text).'<br><br>'.
								$freeshippingHTML .
	    						LANGUAGE.'<br>' .
	    						LANGUAGE_HINT.'<br>' .
	    						draw_small_input_field('language_input', 'DE').'<br>' .
	    						EXPORT_STATUS_TYPE.'<br>'.
	                          	EXPORT_STATUS.'<br>'.
	                          	draw_pull_down_menu('status',$customers_statuses_array, '1').'<br>'.
	                            CURRENCY.'<br>'.
	                            CURRENCY_DESC.'<br>'.
	                            $curr.
	                            CAMPAIGNS.'<br>'.
	                            CAMPAIGNS_DESC.'<br>'.
	                          	draw_pull_down_menu('campaign',$campaign_array).'<br>'.
	                            EXPORT_TYPE.'<br>'.
	                            EXPORT.'<br>'.
	                          	draw_radio_field('export', 'no',false).EXPORT_NO.'<br>'.
	                            draw_radio_field('export', 'yes',true).EXPORT_YES.'<br>'.
	                            '<br>'.button(BUTTON_EXPORT) .
	                            button_link(BUTTON_CANCEL, href_link(FILENAME_MODULE_EXPORT, 'set='.$_GET['set'].'&module=idealo')));

    }

    public function check() {
		if (!isset($this->_check)) {
			$check = $this->db->db_query("SELECT configuration_value from ".TABLE_CONFIGURATION." WHERE configuration_key = 'MODULE_IDEALO_STATUS'");
			$this->_check = $check->_numOfRows;
		}
		return $this->_check;
    }

	/**
	 * Method installs a module in module_export.php
	 */
    public function install() {
		$this->db->db_query("INSERT INTO ".TABLE_CONFIGURATION." (configuration_key, configuration_value,  configuration_group_id, sort_order, set_function, date_added) VALUES ('MODULE_IDEALO_FILE', 'idealo.csv',  '6', '1', '', now())");
		$this->db->db_query("INSERT INTO ".TABLE_CONFIGURATION." (configuration_key, configuration_value,  configuration_group_id, sort_order, set_function, date_added) VALUES ('MODULE_IDEALO_STATUS', 'True',  '6', '1', 'cfg_select_option(array(\'True\', \'False\'), ', now())");
    }

	/**
	 * Method removes a module
	 */
    public function remove() {
		$this->db->db_query("DELETE FROM ".TABLE_CONFIGURATION." WHERE configuration_key in ('".implode("', '", $this->keys())."')");
    }

    public function keys() {
		return array('MODULE_IDEALO_STATUS','MODULE_IDEALO_FILE');
    }
}
?>